Program to find the sum of 2 no’s using stored procedures
import java.sql.*;
public class Sample
{
public static void main(String args[])
{
Connection con=null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:vision","scott","tiger");
CallableStatement cst=con.prepareCall(“{call sum(?,?,?)}”);
cst.setInt(1,10);
cst.setInt(2,20);
cst.registerOutParameter(3,Types.INTEGER);
cst.executeUpdate();
int k=cst.getInt(3);
System.out.println(“Sum of 2nos”+k);
cst.close();
}
catch(ClassNotFoundException e)
{
e.printStackTrace();
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(con!=null)
con.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
}
}
Oracle Procedure
create or replace procedure sum(x in number, y in number ,z out number) is
begin
z:=x+y;
end;
/
Program to Read the data and store the information in to a Table using stored procedures.
import java.sql.*;
import java.util.Scanner;
public class Sample
{
public static void main(String args[])
{
Connection con=null;
Scanner read=new Scanner(System.in);
int rno,m,p,c;
String na=null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:vision","scott","tiger");
System.out.println(“Enter Rolno,name,m,p,c”);
rno=read.nextInt();
na=read.nextLine();
m=read.nextInt();
p=read.nextInt();
c=read.nextInt();
CallableStatement cst=con.prepareCall(“{call stud(?,?,?,?,?,?,?)}”);
cst.setInt(1,rno);
cst.setString(2,na);
cst.setInt(3,m);
cst.setInt(4,p);
cst.setInt(5,c);
cst.registerOutParameter(6,Types.INTEGER);
cst.registerOutParameter(7,Types.INTEGER);
cst.executeUpdate();
int tot=cst.getInt(6);
int avg=cst.getInt(7);
System.out.println(“Total”+tot);
System.out.println(“Average”+avg);
cst.close();
}
catch(ClassNotFoundException e)
{
e.printStackTrace();
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(con!=null)
con.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
}
}
Oracle Procedure
create or replace procedure stud(rno in number, na in varchar2(20) ,m in number,p in number,c in number,tot out number,ave out number) is
begin
tot:=m+p+c;
ave:=tot/3;
insert into student values(rno,na,m,p,c,tot,ave);
end;
/
Program to find the net salary of an employ using stored procedures.
import java.sql.*;
import java.util.Scanner;
public class Sample
{
public static void main(String args[])
{
Connection con=null;
Scanner read=new Scanner(System.in);
int eno,ba,pf,hra,da,net;
String na=null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:vision","scott","tiger");
System.out.println(“Enter empno,name,Basic”);
eno=read.nextInt();
na=read.nextLine();
ba=read.nextInt();
CallableStatement cst=con.prepareCall(“{call emp(?,?,?,?,?,?,?)}”);
cst.setInt(1,eno);
cst.setString(2,na);
cst.setInt(3,ba);
pf=cst.getInt(4);
hra=cst.getInt(5);
da=cst.getInt(6);
net=cst.getInt(7);
cst.registerOutParameter(4,Types.INTEGER);
cst.registerOutParameter(5,Types.INTEGER);
cst.registerOutParameter(6,Types.INTEGER);
cst.registerOutParameter(7,Types.INTEGER);
cst.executeUpdate();
System.out.println(“Name:”+na);
System.out.println(“Pf:”+pf);
System.out.println(“Hra:”+hra);
System.out.println(“Da:”+da);
System.out.println(“Netsaly:”+net);
cst.close();
}
catch(ClassNotFoundException e)
{
e.printStackTrace();
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(con!=null)
con.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
}
}
Oracle Procedure
create or replace procedure emp(eno in number, na in varchar2(20) ,b in number,p out number,h out number,d out number,net out number) is
begin
p:=b*5/100;
h:=b*6/100;
d:=b*7/100;
net:=b+h+d-p;
insert into employ values(eno,na,b,p,h,d,net);
end;
/